#导入数据库连接模块
import pymysql
import datetime

#定义类
class student:
    id = 0
    studentid = ''
    name = ''
    gender = ''
    classid = 0
    classname = ''
    cdate = ''

    def __init__(self,id=0,studentid='',name='',gender='',classid='',classname='',cdate=''):
        self.id = id
        self.studentid = studentid
        self.name = name
        self.gender = gender
        self.classid = classid
        self.classname=classname
        self.cdate = cdate


class dbClass:
    __conn = False
    #构造函数连接数据库
    def __init__(self):
        self.__conn = pymysql.connect(host="localhost", user="root", password="", db="student_db", charset="utf8")
    #析构函数关闭数据库连接
    def __del__(self):
        self.__conn.close()
    
    def Query(self):
        sql="select cid,cname from class_info;"
        cursor = self.__conn.cursor()
        dict = {}
        try:
            cursor.execute(sql)
            list = cursor.fetchall()

            for item in list:
                dict[item[0]] = item[1]
        except Exception as err:
            print("查询信息出错%s"%err)
        cursor.close()
        return dict


#学生表数据库操作类 =====类开始======
class dbstudent:
    __conn = False
    #构造函数连接数据库
    def __init__(self):
        self.__conn = pymysql.connect(host="localhost", user="root", password="", db="student_db", charset="utf8")
    #析构函数关闭数据库连接
    def __del__(self):
        self.__conn.close()
    #查询方法，查询所有，关键字查询
    def Query(self,name=""):
        sql = "select s.*,c.cname as classname from student_info as s left join class_info as c on s.classid=c.cid {};"
        strWhere=""
        if not name.strip()=="":
            strWhere = " where LOCATE('{}',s.`name`) > 0 ".format(name)
        sql = sql.format(strWhere)
        cursor = self.__conn.cursor()
        stuList = []
        try:
            cursor.execute(sql)
            list = cursor.fetchall()

            for stu in list:
                stuList.append(self.__TraselateModel(stu))
        except Exception as err:
            print("查询信息出错%s"%err)
        cursor.close()
        return stuList

    #添加方法
    def Insert(self,stu):
        try:
            sql = "insert into student_info(studentid,`name`,gender,classid,cdate) values('{}','{}','{}','{}',now())".format(stu.studentid,stu.name,stu.gender,stu.classid)
            cursor = self.__conn.cursor()
            cursor.execute(sql)
            self.__conn.commit()
            return  cursor.rowcount
        except Exception as err:
            self.__conn.rollback()
            return 0
    #删除
    def delete(self,id):
        try:
            sql = "delete from student_info where id = {};".format(id)
            cursor = self.__conn.cursor()
            cursor.execute(sql)
            self.__conn.commit()
            return cursor.rowcount
        except Exception as err:
            self.__conn.rollback()
            return 0
    
    #查询学号是否存在
    def Exists(self,studentid):
        try:
            sql = "select count(*) from student_info where id >0 and studentid=%s;"
            cursor = self.__conn.cursor()
            cursor.execute(sql,studentid)
            data = cursor.fetchone()
            return int(data[0])>0
        except Exception as err:
            print("查询数据出错：%s"%err)
    
    #把查询得到元组类型转换为Studentinfo类型
    def __TraselateModel(self,stu):
        id = int(stu[0])
        studentid = str(stu[1])
        name = str(stu[2])
        gender = '男' if stu[3]=='m' else '女'
        classid = int(stu[4])
        classname = str(stu[6])
        cdate = stu[5].strftime('%Y-%m-%d %H:%M:%S')
        return student(id,studentid,name,gender,classid,classname,cdate)
#=====类结束======

#主程序用到的函数

#选择下一步操作
def Continue(msg="请选择接下来要进行的操作："):
    print(msg)
    action = input("1.返回到主菜单        2.直接退出\n")
    if action=="1":
        main()
    elif action=="2":
        print("退出系统")
    else:
        Continue("输入有误，请重新输入：")

#查询用到的函数
def Query():
    '''
    1.查询学生信息
    '''
    action = input("1.查询所有        2.检索\n")
    name = ''
    if action=="2":
        name = input("请输入要查询的学生姓名: ")
    db = dbstudent()
    stuList = db.Query(name)
    if len(stuList) > 0:
        #表头和表格宽度不一样，是因为中文字符问题，还未找到合理的解决方法，只能通过控制宽度尽量靠近
        print("|{0:<5}|{1:<15}|{2:<8}|{3:<8}|{4:<15}|{5:<16}|".format("id","学号","姓名","性别","班级","入学时间"))
        for stu in stuList:
            print("|{0:<5}|{1:<16}|{2:<8}|{3:<8}|{4:<15}|{5:<20}|".format(stu.id,stu.studentid,stu.name,stu.gender,stu.classname,stu.cdate))
    else:
        print("未查询到数据")
    print()
    Continue()

#添加学生信息
def Insert():
    '''
    2.添加学生信息
    '''
    db = dbstudent()
    dbc = dbClass()
    stu = student()
    studentid = input("请输入学号：")
    while studentid.strip()=='':
        studentid = input("学号不能为空，请重新输入：")
    isExist = db.Exists(studentid)
    while isExist:
        studentid = input("学号已存在，请重新输入：")
        isExist = db.Exists(studentid)
    name = input("请输入学生姓名：")
    while name=='':
        name = input("姓名不能为空,请重新输入：")
    gender = input("请输入性别（男/女）:")
    while gender=='':
        gender = input("性别不能为空，请重新输入（男/女）:")
    while not gender =='男' and not gender=='女':
        gender = input("输入不正确，请重新输入（男/女）:")
    dicClass = dbc.Query()
    if len(dicClass)==0:
        print("查询班级信息出错")
    else:
        print("清选择班级：")
        for key in dicClass:
            print(key,".",dicClass[key])
    classid = input()
    while not classid.isdigit():
        classid = input("输入有误,请重新选择班级：")
    while not int(classid) in dicClass:
        classid=input("输入有误,请重新选择班级：")
    stu.classid = classid
    stu.studentid = studentid
    stu.name = name
    stu.gender = 'm' if gender=='男' else 'f'

    result = db.Insert(stu)
    print("成功插入%d条数据"%result)
    Continue()

#删除学生信息
def Delete(msg="请输入需要删除的学生id： "):
    '''
    3.删除学生信息
    '''
    db = dbstudent()
    id = input(msg)
    if not id.isdigit():
        Delete("请输入数字： ")
    try:
        result = db.delete(int(id))
        if result>0:
            print("成功删除%d条信息"%result)
        else:
            Continue("删除失败，请选择接下来要进行的操作：")
    except Exception as err:
        Delete("请输入数字： ")
    Continue()

#主函数
def main(msg="="*20+"欢迎进入学生信息管理系统"+"="*20):
    '''
    主函数
    '''
    print(msg)
    print("1.查询学生信息      2.添加学生信息\n3.删除学生信息      4.退出")
    action = input("清选择要进行的操作:")

    try:
        action = int(action)
        if int(action) ==1:
            Query()
        elif action ==2:
            Insert()
        elif action==3:
            Delete()
        elif action==4:
            print("退出系统")
        else:
            main("输入有误,请重新输入：")
    except ValueError as err:
        main("输入有误，请从下列选项中选择您要进行的操作")

#主函数
main()

